package com.wjf.shortlink.shortlink.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wjf.shortlink.shortlink.dao.entity.LinkAccessStatsDAO;
import com.wjf.shortlink.shortlink.dto.req.ShortLinkGroupStatsReqDTO;
import com.wjf.shortlink.shortlink.dto.req.ShortLinkStatsReqDTO;
import com.wjf.shortlink.shortlink.dto.resp.ShortLinkStatsRespDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

//短链接基础访问监控持久层
public interface LinkAccessStatsMapper extends BaseMapper<LinkAccessStatsDAO> {

    //获取短链接指定日期内的访问统计数据
    @Select("SELECT" +
            "    tlas.date," +
            "    SUM(tlas.pv) AS pv," +
            "    SUM(tlas.uv) AS uv," +
            "    SUM(tlas.uip) AS uip " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tlas.full_short_url = #{param.fullShortUrl} " +
            "    AND tl.gid = #{param.gid} " +
            "    AND tl.del.flag = '0' " +
            "    AND tl.enable_status = #{param.enableStatus} " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "    tlas.full_short_url, tl.gid, tlas.date;"
    )
    List<LinkAccessStatsDAO> listStatsByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);


    //获取短链接指定日期内的小时访问统计数
    @Select("SELECT" +
            "    tlas.hour," +
            "    SUM(tlas.pv) AS pv," +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tlas.full_short_url = #{param.fullShortUrl} " +
            "    AND tl.gid = #{param.gid} " +
            "    AND tl.del.flag = '0' " +
            "    AND tl.enable_status = #{param.enableStatus} " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "    tlas.full_short_url, tl.gid, tlas.hour;"
    )
    List<LinkAccessStatsDAO> listHourStatsByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);

    //获取短链接指定日期内的周访问统计数
    @Select("SELECT" +
            "    tlas.weekday," +
            "    SUM(tlas.pv) AS pv," +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tlas.full_short_url = #{param.fullShortUrl} " +
            "    AND tl.gid = #{param.gid} " +
            "    AND tl.del.flag = '0' " +
            "    AND tl.enable_status = #{param.enableStatus} " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "    tlas.full_short_url, tl.gid, tlas.weekday;"
    )
    List<LinkAccessStatsDAO> listWeekdayStatsByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);

    //根据分组获取短链接指定时间内的基础监控数据
    @Select("SELECT " +
            "     tlas.date, " +
            "     SUM(tlas.pv) AS pv, " +
            "     SUM(tlas.uv) AS uv, " +
            "     SUM(tlas.uip) AS uip " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tl.gid, tlas.date;"
    )
    List<LinkAccessStatsDAO> listStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO shortLinkGroupStatsReqDTO);

    //根据分组获取指定日期内小时的监控数据
    @Select("SELECT " +
            "     tlas.hour, " +
            "     SUM(tlas.pv) AS pv " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tl.gid, tlas.hour;"
    )
    List<LinkAccessStatsDAO> listHourStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO shortLinkGroupStatsReqDTO);


    //根据分组获取指定日期内一周基础监控数据
    @Select("SELECT " +
            "     tlas.weekday, " +
            "     SUM(tlas.pv) AS pv " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_stats tlas ON tl.full_short_url = tlas.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlas.date BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tl.gid, tlas.weekday;"
    )
    List<LinkAccessStatsDAO> listWeekdayStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO shortLinkGroupStatsReqDTO);
}
